Prior art query optimizers typically rely upon a cost model to choose the best query execution plan for any given query. While prior art query optimizers do a remarkably good job of estimating both the cost and the cardinality of most queries, many assumptions underlie this mathematical model. For example, cost estimates are heavily dependent upon the optimizer's estimates for the number of rows (cardinalities), which will result at each step of the query plan. For complex queries involving many predicates and/or operations, these estimates rely upon statistics on the database and modeling assumptions that may or may not be true for a given database. In other words, estimates rely on accurate statistics of database characteristics, beginning with the number of rows for each table or index.
In most optimizers, alternative query execution plans are generated for a given query and the optimizer selects the optimal query plan, which may be the most cost-effective execution plan or one of the more cost-effective execution plans. The optimizer identifies an execution plan (query plan, join plan, or strategy) that reduces the estimated response time of a given query. The response time is the amount of time it takes to complete the execution of the query on the given target system.
One technique of query optimization use is a cost model to estimate the response time of a given query plan and to search the space of query plans to return a plan with a low cost. In the cost-based optimization model, different methods for doing a unit of work is compared and the most efficient method is selected (the plan with the lowest cost). Because the number of alternatives can be quite large, the query optimizer module uses statistics and/or sampling techniques to reduce the search space in optimizing queries.
However, prior art optimizers fail to consider the impact of run-time statistics (real-time system conditions), as they do not manage unforeseen impacts, such as unplanned situations (e.g. a request volume surge, the exhaustion of shared resources, or external conditions like component outages) or even planned situations (e.g. systems maintenance or data load). For example, modern computing systems often execute a variety of requests concurrently and operate in a dynamic environment of cooperative systems, each comprising of numerous hardware components subject to failure or degradation.
Many different types of system conditions or operating environment events can impact negatively the performance of requests currently executing on a computer system. These conditions or events can remain undetected for a prolonged period of time, causing a compounding negative effect on requests executing during that interval. When problematic conditions or events are detected, sometimes in an ad hoc and manual fashion, the computing system administrator may still not be able to take an appropriate course of action, and may either delay corrective action, act incorrectly or not act at all.
A typical impact of not managing for conditions or events is to deliver inconsistent response times to users. For example, often systems execute in an environment of very cyclical usage over the course of any day, week, or other business cycle. If a user ran a report near standalone on a Wednesday afternoon, he/she may expect that same performance with many concurrent users on a Monday morning. However, based on the laws of linear systems performance, a request simply cannot deliver the same response time when running stand-alone as when it runs competing with high volumes of concurrency.
Moreover, overall database performance is best achieved when there is a balanced usage of system resources such that no one resource becomes a bottleneck, wherein the primary resources in a database system include CPU, disk I/O, network, and memory. However, the corrective action for resource bottlenecks is usually limited to alerting the user who, in turn, must reduce the active workload or perform some other manual solution. Given the fact that resource bottlenecks are often a direct result of the characteristics of the execution plans chosen by a query optimizer, it would be helpful if the optimizer could be involved in any corrective action. Unfortunately, prior art optimizers are incapable of directly responding to run-time identified resource bottlenecks. In fact, in most systems, the optimizer is oblivious to run-time conditions and its chosen plans may be making the ongoing condition even worse.
Therefore, there is a need in the art for query optimizations that can respond effectively to resource bottlenecks. Specifically, there is need for optimizers that dynamically adjust query execution plans in response to resource bottlenecks as such bottlenecks occur in real-time. The present invention satisfies this need.